/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.mycompany.h2grade1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.h2.tools.Server;

/**
 *
 * @author JSJSYS
 */
public class H2GRADE {
    //驱动类，H2jar包中可以找到
    static final String DRIVER_CLASS = "org.h2.Driver";
    //数据库连接URL，当前连接的是E:/H2目录下的mydb数据库
     //static final String JDBC_URL = "jdbc:h2:C:/Users/JSJSYS";
    //static final String JDBC_URL = "jdbc:h2:tcp://localhost/mem:";
    // static final String JDBC_URL = "jdbc:h2:file:D:/H2/gacl";
    static final String JDBC_URL = "jdbc:h2:tcp://localhost/~/test";
    //用户名
    static final String USER = "sa";
    //密码
    static final String PASSWORD = "";

    public static void main(String args[]) throws SQLException, Exception {
        // 加载驱动
        Class.forName(DRIVER_CLASS);

        // 创建一个Statement
        try ( // 根据连接URL，用户名，密码获取数据库连接
                Connection conn = DriverManager.getConnection(JDBC_URL, USER, PASSWORD); // 创建一个Statement
                Statement stmt = conn.createStatement()) {
            //如果存在表就先删除
            stmt.execute("DROP TABLE IF EXISTS stu_info");
            //创建stu_info表
            stmt.execute("CREATE TABLE stu_info(id VARCHAR(36) PRIMARY KEY,name VARCHAR(100),sex VARCHAR(4),student_id VARCHAR(50))");
            //新增4 条
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','程凌霄','男','20151101066')");
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','陈秋云','女','20151101026')");
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','黍路','女','20151101056')");
            stmt.executeUpdate("INSERT INTO stu_info VALUES('" + getId() + "','田梦','女','20151101096')");
            p("++++++++++++++++++++++++++++++++++++++显示刚保存的四条数据：");
            show(stmt);
            //修改 黍路 === >黎路
            stmt.executeUpdate("UPDATE stu_info SET name='黎路' WHERE name='黍路'");
            p("=+++++++++++++++++++++++++++++++++修改 黍路 === >黎路,显示结果：");
            show(stmt);
            
            stmt.executeUpdate("DELETE FROM stu_info WHERE name='陈秋云'");
            p("++++++++++++++++++++++++++++++++++++++删除 陈秋云 后，显示结果：");
            show(stmt);
        }
        p("------jdbc -end!!!------");

    }
    static void p(String str) {
        System.out.println(str);
    }

    private static int num = 1;
    private static int getId() {
        return num++;
    }
    //遍历结果集

    private static void show(Statement stmt) throws Exception {
        //查询
        ResultSet rs = stmt.executeQuery("SELECT * FROM stu_info");
        while (rs.next()) {
            p(rs.getString(1) + "\t" + rs.getString("name") + "\t" + rs.getString("sex") + "\t" + rs.getString(4));
        }
    }
    
     static Server s=null;
    private static void  initServer(){
        try { 
            s=  Server.createTcpServer().start();
            p(" create H2 server successfully!!!!!!! ");
        } catch (SQLException ex) {
            Logger.getLogger(H2GRADE.class.getName()).log(Level.SEVERE, null, ex);
            p(" create H2 server failure ");
        }
        
    }
}
